import openpyxl
workbook = openpyxl.load_workbook('tmp.xlsx')

sheet1 = workbook["Sheet1"]
sheet2 = workbook["Sheet2"]
sheet3 = workbook["Sheet3"]
sheet4 = workbook["Sheet4"]


# maxColumn = sheet.max_column
# print('最大列', maxColumn)
# max_row = sheet.max_row
# print('最大行', max_row)
# ll1=[]
# ll2=[]

for s4 in sheet4.iter_rows():
    for s2 in sheet2.iter_rows():
        if s2[0].value==s4[0].value:
            s4[1].value="1"
            s2[1].value="01,02"
        if s2[0].value==s4[2].value:
            s4[3].value="1"
            s2[1].value="03"

    for s3 in sheet3.iter_rows():
        if s3[0].value==s4[0].value:
            s4[1].value="0"
            s3[1].value = "01,02"
        if s3[0].value == s4[2].value:
            s4[3].value="0"
            s3[1].value = "03"
workbook.save("Test.xlsx") #保存
# lu1=[]
# lu2=[]
# for one_column_data in sheet2.iter_rows():
#     lu1.append(one_column_data[0].value)
#
# for one_column_data in sheet3.iter_rows():
#     lu2.append(one_column_data[0].value)
# ll1_result=[]
# print(ll1)
# ccount=0
# for xx in ll1:
#     ++ccount
#     for luu1 in lu1:
#         if luu1==xx:
#             ll1_result.append(xx+"_1")
#     for luu2 in lu2:
#         if luu2==xx:
#             ll1_result.append(xx+"_0")
#     if len(ll1_result)!=ccount:
#         ll1_result.append(xx)
#
#
# print(ll1_result)
# #print(ll2)
